package cn.kys.modules.system.service.impl;


import cn.kys.common.utils.*;
import cn.kys.modules.security.security.vo.JwtUser;
import cn.kys.modules.system.domain.GeomEntity;
import cn.kys.modules.system.pojo.*;
import cn.kys.modules.system.repository.TaskMapper;
import cn.kys.modules.system.repository.ToolsResposity;
import cn.kys.modules.system.service.GetDeptIdByLoginUser;
import cn.kys.modules.system.service.TaskService;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.geom.Polygon;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;

import org.geotools.geometry.jts.JTSFactoryFinder;


import lombok.extern.slf4j.Slf4j;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Primary;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.mongodb.gridfs.GridFsTemplate;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
@Primary
@Service
public class TaskServiceImplzxw implements TaskService {
    @Value("${file.shpUpdate}")
    private String shpUpdate;
    @Autowired
    private ToolsResposity toolsResposity;

    @Autowired
    private TaskMapper taskMapper;


    @Autowired
    private UserDetailsService userDetailsService;

    @Autowired
    MongoTemplate mongoTemplate;


    @Autowired
    private GetDeptIdByLoginUser getDeptIdByLoginUser;
    @Value("${adminUserIncludeStr}")
    private String adminUserIncludeStr;

    // 加部门权限的id
    public Long getDeptId() {
        return getDeptIdByLoginUser.getDeptId();
    }

    // 加部门权限的用户名
    public String getUserName() {
        return getDeptIdByLoginUser.getUserName();
    }


    @Autowired
    GridFsTemplate gridFsTemplate;
    private SimpleDateFormat sdf_ = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public Map addTask(String name, int caseid, String casename, boolean is_public, String lat, String lon) {

        String create_time = sdf_.format(new Date());
        Map returnMap = new HashMap();
        String sql = "SELECT * FROM public.t_task where name = '" + name + "'";
        Map formMap = toolsResposity.exeSql(sql);
        String status = "未开始";
        if (formMap == null) {
            String sql_insert = "INSERT INTO public.t_task(name,caseid,casename,status,is_public,lat,lon,create_time,dept_id) VALUES ('" + name + "'," + caseid + ",'" + casename + "','" + status + "'," + is_public + ",'" + lat + "','" + lon + "','" + create_time + "'," + getDeptId() + ");";
            toolsResposity.exeSql(sql_insert);
            returnMap.put("success", true);
        } else {
            returnMap.put("success", false);
            returnMap.put("msg", "任务已经存在");
        }
        return returnMap;

    }

    @Override
    public Map editTaskStatus(String id, String status) {

        Map returnMap = new HashMap();
        //判断是否有子任务（是否是举证任务）
        String sql = "SELECT * FROM public.t_task where id = '" + id + "'";
        Map taskMap = toolsResposity.exeSql(sql);
        boolean is_public = (boolean) taskMap.get("is_public");
        if (is_public) {
            String sql_update = "UPDATE public.t_task SET  status='" +
                    status + "' WHERE id= '" + id + "'";
            toolsResposity.exeSql(sql_update);
            returnMap.put("success", true);
        } else {
            //查看是否有子任务
            String sql_subtask = "SELECT * FROM public.t_subtask where taskid = '" + id + "'";
            List subTaskList = toolsResposity.exeSqlList(sql_subtask);

            if (subTaskList.size() > 0) {
                //有子任务，可以更新主任务
                String sql_update = "UPDATE public.t_task SET  status='" +
                        status + "' WHERE id= '" + id + "'";
                toolsResposity.exeSql(sql_update);
                //子任务的状态都改成已开始
                String sql_update_subtask = "UPDATE public.t_subtask SET  status='" +
                        status + "' WHERE taskid= '" + id + "'";
                toolsResposity.exeSql(sql_update_subtask);
                returnMap.put("success", true);
            } else {
                returnMap.put("success", false);
                returnMap.put("msg", "请分配人员任务！");
            }
        }
        return returnMap;

    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO 对每个地块审核
     * @Date 9:22 2024/1/18
     **/
    @Override
    public void editPolygonStatus(String subtaskPolygonId, String status) {
//        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
//        Long userid = jwtUser.getId();
        String updatePg = String.format("update t_subtask_geom set check_status='%s' where id=%d", status, Integer.parseInt(subtaskPolygonId));
        toolsResposity.exeSql(updatePg);
        //更新mongodb中的数据
        Query query = new Query(Criteria.where("metadata.globle_edit_geoid").is(subtaskPolygonId));
        Update update = new Update().set("metadata.globle_status", status);
        mongoTemplate.updateMulti(query, update, "tableEntity");
    }


    @Override
    public void addTaskUsers(String _taskid, String userJson) {

        List<Integer> oldList = new ArrayList<>();
        String sql_search = "SELECT * FROM public.t_subtask where taskid = '" + _taskid + "'";
        List list = toolsResposity.exeSqlList(sql_search);
        for (int i = 0; i < list.size(); i++) {
            Map map = (Map) list.get(i);
            int userid = Integer.parseInt(map.get("userid").toString());
            oldList.add(userid);
        }
        String status = "未开始";

        JSONArray jsonArray = JSON.parseArray(userJson);
        String create_time = sdf_.format(new Date());
        List<Integer> newList = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject ob = (JSONObject) jsonArray.get(i);
            String username = ob.getString("username");
            String taskname = ob.getString("taskname");
            int userid = Integer.parseInt(ob.getString("userid"));
            int taskid = Integer.parseInt(ob.getString("taskid"));


            newList.add(userid);
        }

        List deleteList = compare(oldList, newList);
        List addList = compare(newList, oldList);


        for (int i = 0; i < deleteList.size(); i++) {
            int deleteUserid = Integer.parseInt(deleteList.get(i).toString());

            String sql_subtask = "SELECT * FROM public.t_subtask where userid = " + deleteUserid + " and taskid = '" + _taskid + "'";
            Map subtaskMap = toolsResposity.exeSql(sql_subtask);
            int subtaskid = Integer.parseInt(subtaskMap.get("id").toString());

            //删除t_subtask
            String sql_delete = "delete from t_subtask where userid = " + deleteUserid + " and taskid = '" + _taskid + "'";
            toolsResposity.exeSql(sql_delete);
            //删除.t_subtask_polygon
            String sql_delete_subtask_polygon = "delete from t_subtask_polygon where subtaskid = " + subtaskid + " and taskid = '" + _taskid + "'";
            toolsResposity.exeSql(sql_delete_subtask_polygon);
        }

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject ob = (JSONObject) jsonArray.get(i);
            String username = ob.getString("username");
            String taskname = ob.getString("taskname");
            int userid = Integer.parseInt(ob.getString("userid"));
            int taskid = Integer.parseInt(ob.getString("taskid"));

            if (addList.contains(userid)) {
                //添加
                String sql = "SELECT * FROM public.t_subtask where userid = '" + userid + "' and taskid = '" + taskid + "'";
                Map map = toolsResposity.exeSql(sql);
                if (map == null) {
                    String sql_insert = "INSERT INTO public.t_subtask(taskid,taskname,userid,username,status,create_time) VALUES (" + taskid + ",'" + taskname + "'," + userid + ",'" + username + "','" + status + "','" + create_time + "');";
                    toolsResposity.exeSql(sql_insert);
                }
            }

        }

    }

    // 1、2、3 和 1、2、4     先找出3 再找到4
    public List compare(List<Integer> oldList, List<Integer> newList) {
        List resultList = new ArrayList();
        for (int i = 0; i < oldList.size(); i++) {
            int oldI = oldList.get(i);
            if (!newList.contains(oldI)) {
                resultList.add(oldI);
            }
        }
        return resultList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //原来接口名字起的差、写的太乱、废弃——改用下面这2个接口  getAllTasks()  getFinishAndUnFinishTaskNum
     * @Date 9:06 2024/4/28
     **/
    //废弃 废弃 废弃
/*     @Override
   public List getAllTasks() {

        List<Map> listTask = new ArrayList<>();
        if (getUserName().contains(adminUserIncludeStr)) {

            String sql = "SELECT * FROM t_task";
            listTask = toolsResposity.exeSqlList(sql);

            for (Map mapTask : listTask) {

                Integer polygonSum = 0;
                Integer polygonFinishSum = 0;

                Integer taskId = (Integer.parseInt(mapTask.get("id") + ""));//任务id
                String sqlEachTaskCount = String.format("SELECT count(*) from t_subtask_geom where taskid=%d", taskId);
                Map taskMap = toolsResposity.exeSql(sqlEachTaskCount);
                polygonSum = Integer.parseInt(taskMap.get("count") + "");

                //monggoDB中所有subtaskid=186 这个子任务的所有 数量
                Query query = new Query(Criteria.where("metadata.globle_taskid").is(taskId + "")
                        .and("metadata.globle_status").is("已提交"));

                Long hasFinished = mongoTemplate.count(query, JSONObject.class, "tableEntity");
                polygonFinishSum = Integer.parseInt(hasFinished + "");

                mapTask.put("subTaskSum", polygonSum);
                mapTask.put("subTaskFinishSum", polygonFinishSum);
                mapTask.put("subTaskUnFinishSum", polygonSum - polygonFinishSum);

            }
        } else {

            String sql = "SELECT * FROM t_task where dept_id=" + getDeptId();
            listTask = toolsResposity.exeSqlList(sql);

            for (Map mapTask : listTask) {

                Integer polygonSum = 0;
                Integer polygonFinishSum = 0;

                Integer taskId = (Integer.parseInt(mapTask.get("id") + ""));//任务id
                String sqlEachTaskCount = String.format("SELECT count(*) from t_subtask_geom where taskid=%d", taskId);
                Map taskMap = toolsResposity.exeSql(sqlEachTaskCount);
                polygonSum = Integer.parseInt(taskMap.get("count") + "");

                //monggoDB中所有subtaskid=186 这个子任务的所有 数量
                Query query = new Query(Criteria.where("metadata.globle_taskid").is(taskId + "")
                        .and("metadata.globle_status").is("已提交"));

                Long hasFinished = mongoTemplate.count(query, JSONObject.class, "tableEntity");
                polygonFinishSum = Integer.parseInt(hasFinished + "");

                mapTask.put("subTaskSum", polygonSum);
                mapTask.put("subTaskFinishSum", polygonFinishSum);
                mapTask.put("subTaskUnFinishSum", polygonSum - polygonFinishSum);
            }

        }

        return listTask;


    }*/

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO
     * @Date 10:11 2024/4/28
     **/
    @Override
    public List getAllTasks() {
        List<Task> taskList = new ArrayList<>();
        if (getUserName().contains(adminUserIncludeStr)) {
            taskList = taskMapper.queryAllTaskId();
        } else {
            taskList = taskMapper.queryAllTaskIdWithDeptId(getDeptId());
        }
        return taskList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //TODO
     * @Date 10:11 2024/4/28
     **/
    @Override
    public List getFinishAndUnFinishTaskNum() {
        Map mapTask = new HashMap();
        List<Map> listTask = new ArrayList<>();
        List<Task> taskList = new ArrayList<>();
        if (getUserName().contains(adminUserIncludeStr)) {
            taskList = taskMapper.queryAllTaskId();
        } else {
            taskList = taskMapper.queryAllTaskIdWithDeptId(getDeptId());
        }
        for (Task task : taskList) {
            SubtaskGeom subtaskGeom = new SubtaskGeom();
            subtaskGeom.setTaskid(task.getId());
            Integer subTaskCount = taskMapper.querySubTaskCount(subtaskGeom);
            Integer hasSummitTaskCount = taskMapper.queryHasSummitTaskCount(subtaskGeom);
            task.setSubTaskFinishSum(hasSummitTaskCount);
            task.setSubTaskSum(subTaskCount);
            task.setSubTaskUnFinishSum(subTaskCount - hasSummitTaskCount);
        }
        return taskList;
    }


    /**
     * @return
     * @Author zxw
     * @Description //TODO  改原始接口 加权限
     * @Date 2023/12/4  10:14
     **/
    @Override
    public List getTasksByParam(boolean is_public) {
        String sql = "";
        if (getUserName().contains(adminUserIncludeStr)) {
            sql = "SELECT * FROM public.t_task where is_public = " + is_public + " and status != '已结束' order by create_time desc";
        } else {
            sql = String.format("SELECT * FROM public.t_task where is_public = %b and status != '已结束' and dept_id = '%s' order by create_time desc", is_public, getDeptId());
        }

        // and status != '未开始'
//        String sql = "SELECT * FROM public.t_task where is_public = " + is_public + " and status != '已结束' order by create_time desc";
//        return toolsResposity.exeSqlList(sql);
        return toolsResposity.exeSqlList(sql);
    }


    @Override
    public void bingindgPolygonMongodb(String taskid, String subTask) {
        /*JwtUser jwtUser = (JwtUser)userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();*/

        String sql = "SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '" + taskid + "')";
        Map map = toolsResposity.exeSql(sql);
        String layername = map.get("layername").toString();
        //解析subTask json字符串
        JSONArray jsonArray = JSON.parseArray(subTask);

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject ob = (JSONObject) jsonArray.get(i);
            String subtaskid = ob.getString("id");
            String polygons = ob.getString("polygon");

            //先删除，再添加
            String sql_delete = "delete from t_subtask_polygon where subtaskid in (" + subtaskid + ")";
            toolsResposity.exeSql(sql_delete);

            String poly[] = polygons.split("@");
            for (int j = 0; j < poly.length; j++) {
                String tempPoly = poly[j];
                String sql_insert = "INSERT INTO public.t_subtask_polygon(subtaskid,polygon) VALUES (" + subtaskid + ",'" + tempPoly + "');";
                toolsResposity.exeSql(sql_insert);

                //polygon跟图层相交判断，找到需要编辑的图形，插入到mongodb数据库中。
                String sql_geom = "SELECT gid,jsonb_build_object(\n" +
                        "    'type',       'Feature',\n" +
                        "    'gid',         gid,\n" +
                        "    'geometry',   ST_AsGeoJSON(geom)::jsonb,\n" +
                        "    'properties', to_jsonb(row) - 'gid' - 'geom'\n" +
                        ") FROM (SELECT * FROM " + layername + ") row limit 10;";
                List listGeoJson = toolsResposity.exeSqlList(sql_geom);
                for (int k = 0; k < listGeoJson.size(); k++) {
                    Map geoJsonMap = (Map) listGeoJson.get(k);
                    int gid = Integer.parseInt(geoJsonMap.get("gid").toString());
                    String geojson = geoJsonMap.get("jsonb_build_object").toString();
                    GeomEntity geomEntity = new GeomEntity();
                    geomEntity.setCreated(false);
                    geomEntity.setLayername(layername);
                    geomEntity.setGid(gid);
                    geomEntity.setSubtaskid(Integer.parseInt(subtaskid));
                    geomEntity.setGeojson(geojson);
                    mongoTemplate.save(geomEntity);
                }
            }


        }


    }


    @Override
    public void bingindgPolygonPG(String taskid, String subTask) {
        //先判断这个地块有没有被其他人绑定，被其他人绑定了，就禁止绑定 187  386  ""
        String sql = "SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '" + taskid + "')";
        Map map = toolsResposity.exeSql(sql);
        String layername = map.get("layername").toString();
        //解析subTask json字符串
        JSONObject ob = (JSONObject) JSONObject.parse(subTask);
        String subtaskid = ob.getString("id");
        String polygons = ob.getString("polygon");

        //先删除这个子任务已分配的范围，再添加这个子任务新分配的范围
        String sql_delete = "delete from t_subtask_polygon where subtaskid in (" + subtaskid + ")";
        toolsResposity.exeSql(sql_delete);
        //解析新画的范围，生成polygon，插入到t_subtask_polygon中
        GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);
        WKTReader reader = new WKTReader(geometryFactory);
        if (!"".equals(polygons)) {
            String poly[] = polygons.split("@");
            GeometryFactory gf = new GeometryFactory();
            Polygon[] polys = new Polygon[poly.length];
            for (int j = 0; j < poly.length; j++) {
                try {
                    String tempPoly = "POLYGON((" + poly[j] + "))";
                    Polygon p = (Polygon) reader.read(tempPoly);
                    polys[j] = p;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            MultiPolygon mPoly = gf.createMultiPolygon(polys);
            String polygon_text = "st_geomfromtext('" + mPoly.toString() + "'" + ",4326)";
            String sql_insert = "INSERT INTO t_subtask_polygon(subtaskid,taskid,polygon,geom) VALUES (" + subtaskid + "," + taskid + ",'" + mPoly.toString() + "'," + polygon_text + ");";
            toolsResposity.exeSql(sql_insert);
            //删除该任务下已经分配过来的图斑
            String sql_subtask_geom = "delete from t_subtask_geom where subtaskid in (" + subtaskid + ")";
            toolsResposity.exeSql(sql_subtask_geom);
            //在原始数据图层中找和新勾画图斑相交的图斑
            String sql_intersects = "select *,ST_AsText(geom) from " + layername + " where ST_Intersects(" + polygon_text + ",geom)";
            List listGeoJson = toolsResposity.exeSqlList(sql_intersects);
            for (int k = 0; k < listGeoJson.size(); k++) {
                Map geoJsonMap = (Map) listGeoJson.get(k);
                int gid = 0;
                if (geoJsonMap.containsKey("id")) {//主键是id
                    gid = Integer.parseInt(geoJsonMap.get("id").toString());
                } else if (geoJsonMap.containsKey("gid")) {//主键是gid
                    gid = Integer.parseInt(geoJsonMap.get("gid").toString());
                }
                String st_astext = geoJsonMap.get("st_astext").toString();
                String geom_text = "st_geomfromtext('" + st_astext + "'" + ",4326)";
                //找出图层中的图斑再插入t_subtask_geom表中
                String sql_insert_geom = "INSERT INTO public.t_subtask_geom(subtaskid,taskid,gid,layername,iscreated,geom) VALUES (" + subtaskid + "," + taskid + "," + gid + ",'" + layername + "'," + false + "," + geom_text + ");";
                toolsResposity.exeSql(sql_insert_geom);
            }
        } else {
            String delBytaskIdAndSubtaskid = "delete from t_subtask_geom where subtaskid in (" + subtaskid + ") and taskid in (" + taskid + ")";
            toolsResposity.exeSql(delBytaskIdAndSubtaskid);
        }


    }

    /**
     * @return
     * @Author zxw
     * @Description //  更改原来接口 原接口未考虑坐标系问题___且代码完全未封装，乱死了，呵呵
     * @Date 2023/12/5  14:41
     **/
    @Override
    public Map judgeBingdingPolygon(String taskid, String subTask) {
//        String sql = "SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '" + taskid + "')";
        String sql = String.format("SELECT layername FROM public.t_case_editlayer where caseid in (select caseid from t_task where id = '%s' AND dept_id='%s')", taskid, getDeptId());
        Map map = toolsResposity.exeSql(sql);
        String layername = map.get("layername").toString();

        Map returnMap = new HashMap();
        /**
         * 1、同一个人的图斑，先组织成一个MultiPolygon，再跟t_subtask_polygon表中内容比较 同一个任务，不能相交
         * 2、判断最新相交的edit图斑，是否跟已有图斑 t_subtask_geom 的主键gid有相同的，如果有返回。
         */

        JSONObject ob = (JSONObject) JSONObject.parse(subTask);
        String subtaskid = ob.getString("id");
        String polygons = ob.getString("polygon");


        GeometryFactory geometryFactory = JTSFactoryFinder.getGeometryFactory(null);
        WKTReader reader = new WKTReader(geometryFactory);
        String poly[] = polygons.split("@");
        GeometryFactory gf = new GeometryFactory();
        Polygon[] polys = new Polygon[poly.length];
        for (int j = 0; j < poly.length; j++) {
            try {
                String tempPoly = "POLYGON((" + poly[j] + "))";
                Polygon p = (Polygon) reader.read(tempPoly);
                polys[j] = p;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        MultiPolygon mPoly = gf.createMultiPolygon(polys);
        String polygon_text = "st_geomfromtext('" + mPoly.toString() + "'" + ",4326)";

        //查询数量
        String sql_subtask_polygon_count = "select count(*) from " + layername + " where ST_Intersects(" + polygon_text + ",geom)";
        Map countMap = toolsResposity.exeSql(sql_subtask_polygon_count);
        int count = Integer.parseInt(countMap.get("count").toString());
        if (count < 1000) {
//            String sql_subtask_polygon = "select * from t_subtask_polygon where ST_Intersects(" + polygon_text + ",geom) and subtaskid != '" + subtaskid + "' and taskid = '" + taskid + "'";
            String sql_subtask_polygon = "select * from t_subtask_geom where ST_Intersects(" + polygon_text + ",geom) and subtaskid != '" + subtaskid + "' and taskid = '" + taskid + "'";

            List subTaskPolygonList = toolsResposity.exeSqlList(sql_subtask_polygon);

            if (subTaskPolygonList.size() != 0) {
                returnMap.put("isExtentCross", true);
                returnMap.put("isExtentCrossMsg", "新提交的范围与其他人的任务范围有交集，请重新绘制！");
            } else {
                returnMap.put("isExtentCross", false);

                //新的范围，与子任务表相交出来的图斑gid
                String sql_intersects = "select * from " + layername + " where ST_Intersects(" + polygon_text + ",geom)";
                List gidList = toolsResposity.exeSqlList(sql_intersects);

                String sql_subtask_geom = "select gid from t_subtask_geom where taskid = " + taskid + "and subtaskid != " + subtaskid;
                List subTaskGeomList = toolsResposity.exeSqlList(sql_subtask_geom);

                //相交的个数
                int repetition = getRepetition(gidList, subTaskGeomList);
                if (repetition == 0) {//没有相交
                    returnMap.put("isEditGeomCross", false);

                } else {
                    //相交
                    returnMap.put("isEditGeomCross", true);
                    returnMap.put("isEditGeomCrossMsg", "新提交的编辑图斑与其他人的编辑图斑有重合（" + repetition + "个），请重新绘制！");
                }
            }
            returnMap.put("over", false);
            returnMap.put("count", count);
        } else {
            returnMap.put("over", true);
            returnMap.put("count", count);
        }


        return returnMap;
    }

    @Override
    public Map getMVTByPerson(String subtaskid, String tileName, int x, int y, int z) {
        String layername = "t_subtask_geom";

        String sql_mvt = "WITH mvtgeom AS ("
                + "SELECT "
                + "ST_AsMVTGeom("
                + "ST_Transform(geom, 3857),"
                + "ST_TileEnvelope(" + z + "," + x + "," + y + ")"
                + ") as geom "
                + "from " + layername + " where subtaskid = " + subtaskid
                + ")SELECT ST_AsMVT(mvtgeom.*, '" + tileName + "') as mvt from mvtgeom";
        return toolsResposity.exeSql(sql_mvt);
    }

    @Override
    public Map getMVTByLayerName(String layername, String tileName, int x, int y, int z) {

        String sql_mvt = "WITH mvtgeom AS ("
                + "SELECT "
                + "ST_AsMVTGeom("
                + "ST_Transform(geom, 3857),"
                + "ST_TileEnvelope(" + z + "," + x + "," + y + ")"
                + ") as geom "
                + "from " + layername
                + ")SELECT ST_AsMVT(mvtgeom.*, '" + tileName + "') as mvt from mvtgeom";
        return toolsResposity.exeSql(sql_mvt);
    }

    @Override
    public Map getMVTByTask(String taskid, String tileName, int x, int y, int z) {

        String layername = "t_subtask_geom";

        String sql_mvt = "WITH mvtgeom AS ("
                + "SELECT "
                + "ST_AsMVTGeom("
                + "ST_Transform(geom, 3857),"
                + "ST_TileEnvelope(" + z + "," + x + "," + y + ")"
                + ") as geom "
                + "from " + layername + " where taskid = " + taskid
                + ")SELECT ST_AsMVT(mvtgeom.*, '" + tileName + "') as mvt from mvtgeom";
        return toolsResposity.exeSql(sql_mvt);
    }

    @Override
    public Map getMVTByGrid(String tileName, int x, int y, int z, String maxLat, String minLat, String maxLon, String minLon, String metre) {
        double degress = 0.000009;//一米等于多少经纬度

        double dmaxLat = Double.parseDouble(maxLat);
        double dminLat = Double.parseDouble(minLat);
        double dmaxLon = Double.parseDouble(maxLon);
        double dminLon = Double.parseDouble(minLon);

        double distanceLat = dmaxLat - dminLat;
        double distanceLon = dmaxLon - dminLon;


        int width = 10;
        int height = 6;
        double size = distanceLon / width;

        //String sql = "SELECT(ST_PixelAsPolygons (ST_AddBand (ST_SetSRID(ST_MakeEmptyRaster( 50, 30, "+dminLon+", "+dmaxLat+", 0.01),4490),'8BSI' :: TEXT,1,0),1,FALSE)).geom";

        String sql = "SELECT(ST_PixelAsPolygons (ST_AddBand (ST_SetSRID(ST_MakeEmptyRaster( " + width + ", " + height + ", " + dminLon + ", " + dmaxLat + ", " + size + "),4326),'8BSI' :: TEXT,1,0),1,FALSE)).geom";

        String sql_mvt = "WITH mvtgeom AS ("
                + "SELECT "
                + "ST_AsMVTGeom("
                + "ST_Transform(geom, 3857),"
                + "ST_TileEnvelope(" + z + "," + x + "," + y + ")"
                + ") as geom "
                + "from (" + sql + ") as geom"
                + ")SELECT ST_AsMVT(mvtgeom.*, '" + tileName + "') as mvt from mvtgeom";
        return toolsResposity.exeSql(sql_mvt);
    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO  有bug修改接口 主键是id 也可能是gid
     * @Date 2023/12/5  15:01
     **/

    //返回相交图斑数量
    public int getRepetition(List listA, List listB) {
        List list1 = new ArrayList();
        List list2 = new ArrayList();

        int gid = 0;
        for (int i = 0; i < listA.size(); i++) {
            Map map = (Map) listA.get(i);
            if (map.containsKey("id")) {//主键是id
                gid = Integer.parseInt(map.get("id").toString());
            } else if (map.containsKey("gid")) {//主键是gid
                gid = Integer.parseInt(map.get("gid").toString());
            }
            list1.add(gid);
        }
        int gid2 = 0;
        for (int i = 0; i < listB.size(); i++) {
            Map map = (Map) listB.get(i);
            try {
                /*int gid2 = Integer.parseInt(map.get("gid").toString());
                list2.add(gid2);*/
                if (map.containsKey("id")) {
                    gid2 = Integer.parseInt(map.get("id").toString());
                } else if (map.containsKey("gid")) {
                    gid2 = Integer.parseInt(map.get("gid").toString());
                }
                list2.add(gid2);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        List result = new ArrayList();
        for (int i = 0; i < list1.size(); i++) {//遍历list1
            int list1Val = (Integer) list1.get(i);
            if (list2.contains(list1Val)) {//如果存在这个数
                result.add(list1Val);//放进一个list里面，这个list就是交集
            }
        }
        return result.size();
    }

    @Override
    public List getUsers(String roleStr) {
        List list = new ArrayList();
        //String sql = "SELECT * FROM public.users_roles where role_id in (SELECT id FROM public.role where permission = 'cjy')";
        String sql = "SELECT * FROM public.users_roles where role_id in (SELECT id FROM public.role where permission = '" + roleStr + "')";
        List userRoleList = toolsResposity.exeSqlList(sql);
        for (int i = 0; i < userRoleList.size(); i++) {

            if (getDeptId() == 1) {//登录的是 gly_ch   澄海管理员
                Map userRoleMap = (Map) userRoleList.get(i);
                int user_id = Integer.parseInt(userRoleMap.get("user_id").toString());
//            String sql_user = "select * from tuser where id = " + user_id;
                String sql_user = String.format("select * from tuser where id = '%s'", user_id);
                Map userMap = toolsResposity.exeSql(sql_user);
                list.add(userMap);
            } else {
                Map userRoleMap = (Map) userRoleList.get(i);
                int user_id = Integer.parseInt(userRoleMap.get("user_id").toString());
                String sql_user = String.format("select * from tuser where id = '%s' and dept_id='%s'", user_id, getDeptId());
                Map userMap = toolsResposity.exeSql(sql_user);
                list.add(userMap);
            }

        }
        return list;
    }

    @Override
    public List getUsersByTaskId(String taskid) {
        String sql = "SELECT * FROM public.t_subtask where taskid = '" + taskid + "'";
        List list = toolsResposity.exeSqlList(sql);

        List returnList = new ArrayList();
        for (int i = 0; i < list.size(); i++) {
            Map subTaskMap = (Map) list.get(i);
            String subtaskid = subTaskMap.get("id").toString();
            String polygonSql = "SELECT * FROM public.t_subtask_polygon where subtaskid = '" + subtaskid + "'";
            List listExtent = toolsResposity.exeSqlList(polygonSql);
            filterMultiPolygon(listExtent);
            subTaskMap.put("extent", listExtent);
            returnList.add(subTaskMap);
        }
        return returnList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description // 重构改上面的代码，返回该任务下 所有用户的信息和对应绑定的范围
     * @Date 13:30 2024/4/12
     **/
    @Override
    public List<SubtaskVo> getUsersByTaskIdPc(Integer taskid) throws IOException, ParseException {
        List<SubtaskVo> subtaskVoList = new ArrayList<>();
        List<SubtaskPojo> subtaskPojoList = taskMapper.querySubTaskByTaskId(taskid);
        for (SubtaskPojo subtaskPojo : subtaskPojoList) {
            SubtaskVo subtaskVo = new SubtaskVo();
            Integer subTaskId = subtaskPojo.getId();
            Integer taskId = subtaskPojo.getTaskid();
            List<SubtaskGeom> subtaskGeomList = taskMapper.querySubtaskGeomBy2ID(taskId, subTaskId);
            for (SubtaskGeom subtaskGeom : subtaskGeomList) {
                String geoJSON = Utils.convertWKTtoGeoJSON(subtaskGeom.getGeomStr());
                subtaskGeom.setGeomStr(geoJSON);
            }
            subtaskVo.setSubtaskPojo(subtaskPojo);
            subtaskVo.setListSubtaskGeom(subtaskGeomList);
            subtaskVoList.add(subtaskVo);
        }
        return subtaskVoList;

    }


    @Override
    public Map editTaskContent(String id, String name, int caseid, String casename, boolean is_public, String lat, String lon) throws Exception {
        Map resultMap = new HashMap();

        List<Map> nameList = toolsResposity.queryNameFromTask(caseid);
        for (Map map : nameList) {
            //判断更新的是不是同一条，是的话
            String idStr = map.get("id").toString();
            String nameStr = map.get("name").toString();
            if (!id.equals(idStr) && name.equals(nameStr)) {
                resultMap.put("success", false);
                resultMap.put("msg", name + "已经存在");
                return resultMap;
            }
        }

        String sql_update = "UPDATE public.t_task SET  name='" +
                name + "', caseid=" + caseid + ", casename = '" + casename + "', is_public = " + is_public + ", lat = '" + lat + "', lon = '" + lon + "' WHERE id= '" + id + "'";
        toolsResposity.exeSql(sql_update);
        resultMap.put("success", true);

        return resultMap;

    }

    @Override
    public void delTaskByIds(String ids) {

        String idArr[] = ids.split(",");
        for (int j = 0; j < idArr.length; j++) {
            String id = idArr[j];
            //删除t_subtask_polygon表中的数据
            String sql_subtask_polygon = "delete from t_subtask_polygon where subtaskid in (select id from t_subtask where taskid ='" + id + "')";
            toolsResposity.exeSql(sql_subtask_polygon);
            //删除t_subtask_geom表中的数据
            String sql_t_subtask_geom = "delete from t_subtask_geom where subtaskid in (select id from t_subtask where taskid ='" + id + "')";
            toolsResposity.exeSql(sql_t_subtask_geom);
            //删除mongodb表中的数据
            //删除mongodb表中的编辑图斑
            String sql = "select id from t_subtask where taskid ='" + id + "'";
            List list = toolsResposity.exeSqlList(sql);
            /*for(int i=0;i<list.size();i++){
                Map map = (Map)list.get(i);
                int subtaskid = Integer.parseInt(map.get("id").toString());
                Query query = new Query(Criteria.where("id").is(subtaskid));
                mongoTemplate.findAndRemove(query, GeomEntity.class,"geomEntity");
            }*/
            //删除mongodb表中的table内容
            for (int i = 0; i < list.size(); i++) {
                Map map = (Map) list.get(i);
                String subtaskid = map.get("id").toString();
                Query query = new Query(Criteria.where("metadata.globle_subtaskid").is(subtaskid));
                mongoTemplate.findAndRemove(query, JSONObject.class, "tableEntity");
            }

            //删除图片
            for (int i = 0; i < list.size(); i++) {
                Map map = (Map) list.get(i);
                String subtaskid = map.get("id").toString();

                Criteria criteriaFile = Criteria.where("metadata.globle_subtaskid").is(subtaskid);

                Query query = new Query(criteriaFile);
                gridFsTemplate.delete(query);
            }


            //删除t_subtask表中的数据
            String sql_subtask = "delete from t_subtask where taskid ='" + id + "'";
            toolsResposity.exeSql(sql_subtask);

            //删除t_task表中的数据
            String sql_task = "delete from t_task where id ='" + id + "'";
            toolsResposity.exeSql(sql_task);
        }

    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO 改代码  新增返回下拉列表
     * @Date 2023/12/4  15:53
     **/
    @Override
    public Map findFormContentByCaseId(String subtaskid, String edit_geoid, String caseid) {
        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

        Map returnMap = new HashMap();
        //结构
        String sql = "select * from t_form_content where formid in (SELECT formid FROM public.t_case_form where caseid = '" + caseid + "')";
        List<Map> structList = toolsResposity.exeSqlList(sql);
        returnMap.put("struct", structList);

        //获取下拉列表枚举值开始
        List<String> formcontentidList = new ArrayList();
        for (Map map : structList) {
            map.get("type");
            System.out.println(map.get("type"));
            if ("下拉列表".equals(map.get("type"))) {
                String sql1 = String.format("select distinct(formcontentid) from t_form_content where type='%s'", "下拉列表");
                List<Map> enumList = toolsResposity.exeSqlList(sql1);
                for (Map map1 : enumList) {
                    formcontentidList.add(map1.get("formcontentid").toString());
                }
            }

        }
        System.out.println(formcontentidList);
        for (String formcontentidStr : formcontentidList) {//80 82

            for (Map map : structList) {
                String formcontentid = map.get("formcontentid").toString();

                if (formcontentidStr.equals(formcontentid)) {
                    String sql2 = String.format("select enum from t_formcontent where id ='%s'", formcontentid);
                    Map enumMapResult = toolsResposity.exeSql(sql2);
                    map.put("typeList", enumMapResult);
                }
            }
        }
        //获取下拉列表枚举值结束

        //内容
        Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
        criteria.and("metadata.globle_edit_geoid").is(edit_geoid);
        criteria.and("metadata.globle_userid").is(userid);
        Query query = new Query(criteria);
        List contentList = mongoTemplate.find(query, JSONObject.class, "tableEntity");
        if (contentList.size() == 0) {
            returnMap.put("hasContent", false);
        } else {
            returnMap.put("hasContent", true);
        }
        returnMap.put("content", contentList);
//        returnMap.put("typeList", enumMap);
        return returnMap;
    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO   为什么又是 4490了?     改bug
     * @Date 2023/12/5  15:12
     **/
    @Override
    public Map findGeomByPos(String taskid, String subtaskid, String lon, String lat) {

        JwtUser jwtUser = (JwtUser) userDetailsService.loadUserByUsername(SecurityUtils.getUsername());
        Long userid = jwtUser.getId();

//        String sql = "select * from t_case_editlayer where caseid in (SELECT caseid FROM public.t_task where id = " + taskid + ")";
//        Map mapLayerName = toolsResposity.exeSql(sql);
        //String layername = mapLayerName.get("layername").toString();
        String layername = "t_subtask_geom";
//        String sql_geom = "select *,st_astext(ST_Transform(t.geom,4490))  from " + layername + " t where ST_Contains(ST_Transform(t.geom,4490),ST_PointFromText('POINT(" + lon + " " + lat + ")',4490))";
        String sql_geom = "select *,st_astext(ST_Transform(t.geom,4326))  from " + layername + " t where" +
                " ST_Contains(ST_Transform(t.geom,4326),ST_PointFromText('POINT(" + lon + " " + lat + ")',4326)) and taskid=" + taskid;
        ;
        Map map = toolsResposity.exeSql(sql_geom);

        if (map == null) {
            Map returnMap = new HashMap();
            returnMap.put("success", false);
            return returnMap;
        } else {
            String edit_geoid = map.get("id").toString();
            Criteria criteria = Criteria.where("metadata.globle_subtaskid").is(subtaskid);
            criteria.and("metadata.globle_edit_geoid").is(edit_geoid + "");
            criteria.and("metadata.globle_userid").is(Integer.parseInt(userid + ""));
            Query query1 = new Query(criteria);
            JSONObject jsonObject = mongoTemplate.findOne(query1, JSONObject.class, "tableEntity");
            if (jsonObject != null) {
                System.out.println(jsonObject.toJSONString());
            }
            map.put("table", jsonObject);
            map.put("success", true);


            return filterMap(map);
        }
    }

    @Override
    public void exportShpPolygon(HttpServletRequest request, HttpServletResponse response, String subtaskid) {
        String sql = "select * from t_gps where subtaskid = '" + subtaskid + "' ORDER BY id ASC";
        List list = toolsResposity.exeSqlList(sql);
        Long startTs = System.currentTimeMillis();
        String shpName = startTs + ".shp";
        String shpFile = shpUpdate;
        ShpWriter.WriteVectorFile(list, shpFile + shpName);
        //压缩
        try {
            //遍历shp 压缩
            File file = new File(shpFile);        //获取其file对象
            File[] fs = file.listFiles();    //遍历path下的文件和目录，放在File数组中
            List<String> sourceFilePaths = new ArrayList<String>();
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {
                    sourceFilePaths.add(f.getAbsolutePath());
                }
            }
            String zipName = startTs + ".zip";
            String zipTempFilePath = shpFile + zipName;
            //调用压缩
            int s = Zip.compress(sourceFilePaths, zipTempFilePath, false);
            System.out.println("成功压缩" + s + "个文件");

            //上传
            File upFile = new File(zipTempFilePath);
            FileUtil.downloadFileAndDelete(request, response, upFile, true);

            //删除shp数据
            for (File f : fs) {                    //遍历File[]数组
                if (f.getPath().contains(startTs + "")) {

                    File tempFile = new File(f.getAbsolutePath());
                    tempFile.delete();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    private void filterMultiPolygon(List list) {

        for (int i = 0; i < list.size(); i++) {
            Map map = (Map) list.get(i);
            String multiPolygon = map.get("polygon").toString();

            String st_astext = multiPolygon;

            List listPolygon = new ArrayList();

            if (st_astext.contains("MULTIPOLYGON")) {
                st_astext = st_astext.substring(16, st_astext.length() - 3);
            } else {
                st_astext = st_astext.substring(9, st_astext.length() - 2);
            }

            String[] polygons = st_astext.split("\\)\\), \\(\\(");

            for (int j = 0; j < polygons.length; j++) {
                String polygon = polygons[j].trim();
                if (polygon.contains("),(")) {
                    String[] polygonHole = polygon.split("\\),\\(");
                    listPolygon.add(polygonHole);
                } else {
                    listPolygon.add(polygon);
                }
            }

            map.put("geo", listPolygon);
        }


    }

    private Map filterMap(Map returnMap) {

        String st_astext = returnMap.get("st_astext").toString();

        List listPolygon = new ArrayList();

        if (st_astext.contains("MULTIPOLYGON")) {
            st_astext = st_astext.substring(15, st_astext.length() - 3);
        } else {
            st_astext = st_astext.substring(9, st_astext.length() - 2);
        }

        String[] polygons = st_astext.split("\\)\\), \\(\\(");

        for (int j = 0; j < polygons.length; j++) {
            String polygon = polygons[j].trim();
            if (polygon.contains("),(")) {
                String[] polygonHole = polygon.split("\\),\\(");
                listPolygon.add(polygonHole);
            } else {
                listPolygon.add(polygon);
            }
        }

        returnMap.put("geo", listPolygon);

        return returnMap;

    }

    @Override
    public List list() {
        return taskMapper.list();
    }

    @Override
    public List listCondition(String name, String casename, boolean isPublic) {
        return taskMapper.listContidon(name, casename, isPublic);
    }

    @Override
    public Long countUncommit(Integer taskid) {
        /*//monggoDB中所有这个任务的所有数量
        Query query = new Query(Criteria.where("metadata.globle_taskid").is(taskid + "")
                .and("metadata.globle_status").ne("已提交"));
        Long countUncommit = mongoTemplate.count(query, JSONObject.class, "tableEntity");
*/

        return taskMapper.countUncommit(taskid);

    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //采集任务分配显示图斑列表_没绑定的
     * @Date 8:38 2024/4/10
     **/
    @Override
    public List<Map> showPolygonList(SubtaskGeom subtaskGeom) {
        Integer taskId = subtaskGeom.getTaskid();
        String layerNameById = taskMapper.getLayerNameById(taskId);
        List<Map> resultList = new ArrayList<>();

        //先判断taskid在不在t_subtask_geom表中  在的话 就表明已经分配任务了
        boolean exist = taskMapper.taskIdExistSubtaskGeomTable(taskId);
        //在
        if (exist) {
            List<Map> subtaskGeomList = taskMapper.showUnBindPolygonList(layerNameById, taskId);
            //如果有不相交的图斑，把不相交的图斑 返给前台
            if (subtaskGeomList.size() >= 0) {
                for (Map map : subtaskGeomList) {
                    map.put("layername", layerNameById);
                }
                resultList = subtaskGeomList;
            }
            //不在
        } else {
            resultList = taskMapper.showUnBindPolygonWholeTable(layerNameById);
            for (Map map : resultList) {
                map.put("layername", layerNameById);
            }
        }
        return resultList;
    }

    /**
     * @return
     * @Author zhaoxw
     * @Description //采集任务分配显示图斑列表后分配未绑定图斑
     * @Date 9:12 2024/4/12
     **/
    @Override
    public List<Integer> assignUnboundPolygon(LayerDto layerDto) {

        ArrayList<Integer> resultList = new ArrayList();
        List<Integer> gids = layerDto.getGid();

        for (Integer gid : gids) {

            SubtaskDto subtaskDto = new SubtaskDto();
            subtaskDto.setUserid(layerDto.getUserid());
            subtaskDto.setTaskid(layerDto.getTaskid());
            Integer subTaskId = taskMapper.querySubTaskId(subtaskDto);
            String wkt = taskMapper.queryGeomFromImportShpByGid(layerDto.getLayername(), gid);

            String geom = "";
            String wktMultipolygon = "";
            if (wkt.toUpperCase().contains("POLYGON") && !wkt.toUpperCase().contains("MULTI")) {
                wktMultipolygon = wkt.replace("POLYGON ((", "MULTIPOLYGON (((").replace("))", ")))");
                geom = "ST_SetSRID(ST_GeomFromText('" + wktMultipolygon + "'), 4326)";
            } else {
                wktMultipolygon = wkt;
                geom = "ST_SetSRID(ST_GeomFromText('" + wktMultipolygon + "'), 4326)";
            }

            SubtaskGeom subtaskGeom = new SubtaskGeom();
            subtaskGeom.setSubtaskid(subTaskId);
            subtaskGeom.setGid(gid);
            subtaskGeom.setLayername(layerDto.getLayername());
            subtaskGeom.setIscreated(false);
            subtaskGeom.setGeomStr(geom);
            subtaskGeom.setTaskid(layerDto.getTaskid());
            subtaskGeom.setCheck_status(null);
            subtaskGeom.setCreatetime(LocalDateTime.now());
            Integer id = taskMapper.insertSubtaskGeom(subtaskGeom);
            //把勾选的矢量面插入表t_subtask_polygon 中  目的是 防止其他接口报错 这个表原来设计的是自己勾画的图斑保存进来
            SubtaskPolygon subtaskPolygon = new SubtaskPolygon();
            subtaskPolygon.setPolygon(wktMultipolygon);
            subtaskPolygon.setSubtaskid(subTaskId);
            subtaskPolygon.setTaskid(layerDto.getTaskid());
            subtaskPolygon.setWkt(geom);
            taskMapper.isertSubtaskPolygon(subtaskPolygon);

            resultList.add(id);
        }

        return resultList;
    }


}

